package de.unistuttgart.iaas.interaction.mail.database;

import java.sql.*;

import javax.mail.Session;

import org.apache.log4j.Logger;

import de.unistuttgart.iaas.interaction.mail.AdminConnector;
import de.unistuttgart.iaas.interaction.mail.util.ConfigFileHandler;
import de.unistuttgart.iaas.interaction.mail.util.ErrorHandler;

/** 
 * This class creates the connection to database and closes it.
 * @author Lina Sun
 *
 */
public class MySQLAccess {
	
	private Connection connect= null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	private static Logger logger = Logger.getLogger(MySQLAccess.class);

	
	/**
	 * This method connects the database maildb and creates five tables if these tables don't exist.
	 * 1.table--mailregister
	 * 2.table--mailbox
	 * 3.table--sentmails
	 * 4.table--mailattachments
	 * 5.table--commserv_ad_gmx_de(this tablename can be changeable according to the account for Administrator)
	 * @param config the configFileHandler
	 * @return Connection connect
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * 
	 */
	public Connection connDataBase(ConfigFileHandler config) throws Exception{
			
		String db_username = config.getDB_username();
		String db_password = config.getDB_password();
		String db_url = config.getDB_url();
		String table_mailbox = config.getTable_mailbox();
		String table_sentmails = config.getTable_sentmails();
		String table_attachments = config.getTable_mailattachments();
		String table_register = config.getTable_mailregister();
		String table_mailids = config.getMailAddress();
		String temp = table_mailids.replace("@", "_");
		table_mailids = temp.replace(".","_");
		

		try{
			//load the MySQL drive
			Class.forName("com.mysql.jdbc.Driver");		
			connect = DriverManager.getConnection(db_url, db_username, db_password);
		
			String table1Sql = "create table if not exists maildb."+table_mailbox +"("
			+ "mail_id varchar(45) not null primary key, mail_replyid varchar(45),mail_receivedDate timestamp not null,"
			+ "mail_from varchar(45) not null, mail_from_username varchar(45) not null, mail_to varchar(45) not null, mail_subject varchar(200) not null,"
			+ "mail_message text not null, mail_class varchar(45), mail_state varchar(45) not null);";
		
			String table2Sql = "create table if not exists maildb."+ table_sentmails +"("
		     + "mail_id varchar(45) not null primary key,mail_to varchar(45) not null, mail_from varchar(45) not null, mail_subject varchar(200) not null,"
		     + "mail_message text not null, mail_sentDate timestamp not null, mail_response varchar(45) not null,"
		     + "mail_response_pattern varchar(200),mail_timeout timestamp, mail_class varchar(45), mail_state varchar(45));";
			
			String table3Sql = "create table if not exists maildb."+ table_attachments + "(id int not null primary key auto_increment,"
		     + "mail_id varchar(45) not null,filename varchar(100) not null,content mediumblob not null);";
			
			String table4Sql = "create table if not exists maildb."+ table_mailids +"(id int not null primary key auto_increment,"
			+ "pop3_uid varchar(45));";
			
			String table5Sql = "create table if not exists maildb."+ table_register +"(id int not null primary key auto_increment,"
			+ "mailaddress varchar(45) not null, username varchar(45) not null);";
		
			if(connect!=null){
				statement = connect.createStatement();
				statement.executeUpdate(table1Sql);
				statement.executeUpdate(table2Sql);
				statement.executeUpdate(table3Sql);
				statement.executeUpdate(table4Sql);
				statement.executeUpdate(table5Sql);

			}else{
				
				logger.error("No database.");
				System.out.println("No database.");
				
			}
		
			}catch(ClassNotFoundException e){
				
				//ErrorHandler
			   	Session session = AdminConnector.SendConnector(config);
			   	ErrorHandler.sendErrorMessageForConnDB(session, config, e);
				logger.error("Failed to load the MySQL drive");
			}
			catch (SQLException e1) {
			    // TODO Auto-generated catch block
				
				//ErrorHandler
				
			   	Session session = AdminConnector.SendConnector(config);
			   	ErrorHandler.sendErrorMessageForSQL(session, config, e1);
				logger.error("Failed to connect database.");
			   }

		return connect;

	}

	// You need to close the resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
	


}
